package com.pk.platform.business.charge.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.pk.platform.business.charge.dao.ITuitionDao;
import com.pk.platform.business.charge.vo.TuitionVO;
import com.pk.platform.business.core.dao.impl.GenericDaoImpl;
import com.pk.platform.util.DateConverter;
import com.pk.platform.util.StringConverter;
import com.pk.platform.util.constant.ChargeConstant;
import com.pk.platform.util.constant.Constant;
import com.pk.platform.util.page.ListPage;
import com.pk.platform.util.page.Pager;

public class TuitionDaoImpl extends GenericDaoImpl implements ITuitionDao {

	public ListPage queryTuition(TuitionVO tvo, Pager pager) {
		StringBuffer sql = new StringBuffer();
		List<Object> param = new ArrayList<Object>();
		sql.append("select t.*, td.intAmount, c.className, ch.childName from tuition t,");
		sql.append(" (select tuitionId, sum(round(amount, 0)) intAmount from tuition_detail group by tuitionId) td,");
		sql.append(" cla c, child ch where t.id = td.tuitionId and t.classId = c.id and t.childId = ch.id");
		if(tvo.getTuition().getIsDel() != null)
		{
			sql.append(" and t.isDel = ?");
			param.add(tvo.getTuition().getIsDel());
		}
		if(StringConverter.isNotNull(tvo.getTuition().getTuitionNo()))
		{
			sql.append(" and t.tuitionNo like ?");
			param.add("%" + tvo.getTuition().getTuitionNo() + "%");
		}
		if(StringConverter.isNotNull(tvo.getPayDateStart()))
		{
			sql.append(" and t.payDate >= ?");
			param.add(tvo.getPayDateStart());
		}
		if(StringConverter.isNotNull(tvo.getPayDateEnd()))
		{
			sql.append(" and t.payDate <= ?");
			param.add(tvo.getPayDateEnd() + Constant.LATEST_TIME_OF_DAY);
		}
		if(StringConverter.isNotNull(tvo.getChildName()))
		{
			sql.append(" and ch.childName like ?");
			param.add("%" + tvo.getChildName() + "%");
		}
		if(StringConverter.isNotNull(tvo.getChildNum()))
		{
			sql.append(" and ch.childNum = ?");
			param.add(tvo.getChildNum());
		}
		if(StringConverter.isNotNull(tvo.getClassId()))
		{
			sql.append(" and t.classId = ?");
			param.add(tvo.getClassId());
		}
		if(tvo.getTotalAmountStart() != null)
		{
			sql.append(" and t.totalAmount >= ?");
			param.add(tvo.getTotalAmountStart());
		}
		if(tvo.getTotalAmountEnd() != null)
		{
			sql.append(" and t.totalAmount <= ?");
			param.add(tvo.getTotalAmountEnd());
		}
		if(StringConverter.isNotNull(tvo.getKindergartenId()))
		{
			sql.append(" and t.kgId = ?");
			param.add(tvo.getKindergartenId());
		}
		sql.append(this.getKgData("t", Constant.SQL));
		return this.getListPage(sql.toString(), param.toArray(), pager);
	}

	public int queryTuitionCountByChild(String childId) {
		String sql = "select count(0) from tuition where isDel = 0 and childId = ?";
		return this.getSjt().queryForInt(sql, childId);
	}
	
	public int getMaxNoByKgCode(String kgCode)
	{
		String date = DateConverter.dateToString(new Date(), DateConverter.FORMAT_SEVEN);
		String code = kgCode + "_" + date;
		int len = code.length();
		int index = len + 2;
		StringBuffer sql = new StringBuffer();
		sql.append("select max(substr(tuitionNo, ").append(index).append(")) as maxNo from tuition where tuitionNo like '").append(code).append("%';");
		List<Map<String, Object>> list = this.getSjt().queryForList(sql.toString());
		if(list.size() > 0 && list.get(0).get("maxNo") != null)
		{
			return Integer.parseInt(list.get(0).get("maxNo").toString());
		}
		return 0;
	}

	/**
	 * 
	 * @param isBj  是否是保教费和伙食费
	 * @return;
	 */
	public List<Map<String, Object>> getTitle(String tuitionId,int  isBj){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT distinct ci.indexName  FROM tuition_detail td left join charge_index ci on td.chargeindexid = ci.id where td.tuitionid='"+tuitionId+"' ");
		sql.append(" and ci.indexName "+(isBj==1?"":" not ")+" in ('" + ChargeConstant.SCHOOLING + "','" + ChargeConstant.MEAL + "') ");
		//System.out.println("查询是否保教sql:::::::::"+sql.toString());
		return this.getSjt().queryForList(sql.toString(), new Object[]{ });
	}
	
	/**
	 * 
	 * @param isBj  是否是保教费和伙食费
	 * @return;
	 */
	public float getsubTotal(String tuitionId,int  isBj){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT case when sum(amount) is null then 0 else sum(amount) end as amount FROM tuition_detail td left join charge_index ci on td.chargeindexid = ci.id where td.tuitionid='"+tuitionId+"' ");
		sql.append(" and ci.indexName "+(isBj==1?"":" not ")+" in ('" + ChargeConstant.SCHOOLING + "','" + ChargeConstant.MEAL + "') ");
		return Float.parseFloat(this.getSjt().queryForMap(sql.toString(), new Object[]{ }).get("amount").toString());
	}
	
	/**
	 * 修改之前按月group
	 * @param tuitionId
	 * @param isBj
	 * @return
	 */
	public List<Map<String, Object>> getReportListByMonth(String tuitionId,int  isBj){
		List<Map<String, Object>> title;
		Map map; 
		StringBuffer sql = new StringBuffer();
		title=getTitle(tuitionId,isBj);
		sql.append("select DATE_FORMAT(MIN(x.tuitionDateStart), '%Y-%m-%d'),DATE_FORMAT(Max(x.tuitionDateEnd), '%Y-%m-%d') ");
			
			for (int s = 0; s < title.size(); s++) {
				map = new HashMap();
				map = title.get(s);
				sql.append(",max(case when x.indexName='"+map.get("indexName")+"' then x.amount end) '"+map.get("indexName")+"' ");
			}
			sql.append("from( ");
			sql.append("select tu.*,ci.indexName from (SELECT chargeindexid,sum(amount) amount,month,tuitionDateStart,tuitionDateEnd FROM tuition_detail ");
			sql.append("where tuitionid='"+tuitionId+"' ");
			sql.append("group by chargeindexid,month) tu left join charge_index ci on tu.chargeindexid=ci.id where indexname  "+(isBj==1?"":" not ")+" in ('" + ChargeConstant.SCHOOLING + "','" + ChargeConstant.MEAL + "') ");
			sql.append(") x group by month ");
		//System.out.println("得到交费数据sql:"+sql.toString());
		return this.getSjt().queryForList(sql.toString(), new Object[]{ });
	}
	
	/**
	 * 修改之后不按月group
	 */
	public List<Map<String, Object>> getReportList(String tuitionId,int  isBj){
		List<Map<String, Object>> title;
		Map map; 
		StringBuffer sql = new StringBuffer();
		title=getTitle(tuitionId,isBj);
		sql.append("select DATE_FORMAT(MIN(x.tuitionDateStart), '%Y-%m-%d') tuitionDateStart,DATE_FORMAT(Max(x.tuitionDateEnd), '%Y-%m-%d') tuitionDateEnd ");
			
			for (int s = 0; s < title.size(); s++) {
				map = new HashMap();
				map = title.get(s);
				sql.append(",max(case when x.indexName='"+map.get("indexName")+"' then round(x.amount, 0) end) '"+map.get("indexName")+"' ");
			}
			sql.append("from( ");
			sql.append("select tu.*,ci.indexName from (SELECT chargeindexid,sum(amount) amount,MIN(tuitionDateStart) tuitionDateStart,Max(tuitionDateEnd) tuitionDateEnd  FROM tuition_detail ");
			sql.append("where tuitionid='"+tuitionId+"' ");
			sql.append("group by chargeindexid) tu left join charge_index ci on tu.chargeindexid=ci.id where indexname  "+(isBj==1?"":" not ")+" in ('" + ChargeConstant.SCHOOLING + "','" + ChargeConstant.MEAL + "') ");
			sql.append(") x  ");
		//System.out.println("得到交费数据sql:"+sql.toString());
		return this.getSjt().queryForList(sql.toString(), new Object[]{ });
	}
}
